﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using CS2102_Music.Logic.LogicModels;

namespace CS2102_Music.Logic
{
    public partial class Logic
    {
        // Functions with SQL
        public List<ArtistL> GetAllArtist()
        {
            List<ArtistL> list = new List<ArtistL>();
            ArtistL artist = new ArtistL();

            // get artists
            string query = "SELECT * FROM artist";

            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query);

            while (result.Read())
            {
                artist.Name = result.GetString(0);
                artist.Photo = result.GetString(1);
                artist.ID = result.GetInt32(2);

                list.Add(artist);

                artist = new ArtistL();
            }

            result.Close();


            return list;
        }
        public List<ArtistL> Top5Artists(out List<int> number)
        {
            number = new List<int>();
            int num = 0;
            List<ArtistL> top5 = new List<ArtistL>();
            ArtistL artist = new ArtistL();
            string query = "SELECT s.artist, a.name, COUNT(*) FROM sing s, buysong bs, song_owned_by sob, artist a WHERE s.song = bs.song AND bs.song = sob.songID AND s.artist = a.artistID AND sob.active = 'Y' GROUP BY s.artist, a.name ORDER BY COUNT(*) DESC, s.artist LIMIT 5";
            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query);

            while (result.Read())
            {
                artist.ID = result.GetInt32(0);
                artist.Name = result.GetString(1);

                num = result.GetInt32(2);

                number.Add(num);
                top5.Add(artist);

                artist = new ArtistL();
            }

            result.Close();


            return top5;
        }
        public void CreateArtist(string name)
        {
            int id = 0;

            // get id
            MySqlDataReader result = databaseConnector.ExecuteReaderQuery("select COUNT(*) from artist"
                                                                           );
            while (result.Read())
            {
                id = result.GetInt32(0);
            }

            result.Close();

            // insert 
            string query = "insert into artist(name, img_url, artistid)  values (@name, 'test', @id)";

            databaseConnector.ExecuteNonQuery(query
                                              , new QueryParameter("@name", name)
                                              , new QueryParameter("@id", id.ToString())
                                              );

        }

    }
}